import pandas as pd

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

import util.lib as lib
import util.mydecorator as mydecorator

import format

@mydecorator.calTime
def job():
    # 加载 Excel 文件
    wb = load_workbook(lib.data_path + 'drawdown.xlsx')

    for sheet_name in wb.sheetnames:
        # 获取当前 Sheet
        sheet = wb[sheet_name]

        # 冻结首行（第一行）
        sheet.freeze_panes = 'A2'  # 表示冻结 A2 单元格上方的行（即第一行）

        # 设置第一列的宽
        sheet.column_dimensions['A'].width = 20 


        sheet.conditional_formatting.add('D2:D5100', format.drawdown_rule)

        sheet.conditional_formatting.add('E2:E5100', format.pct_rule)
        sheet.conditional_formatting.add('F2:F5100', format.pct_rule)
        sheet.conditional_formatting.add('G2:G5100', format.pct_rule)

        # lp
        sheet.conditional_formatting.add('H2:H5100', format.lplt10_rule)
        sheet.conditional_formatting.add('H2:H5100', format.lplt20_rule)
        sheet.conditional_formatting.add('H2:H5100', format.lpgt99_rule)

        # rellp
        sheet.conditional_formatting.add('I2:I5100', format.rellp_rule)

        sheet.conditional_formatting.add('J2:J5100', format.pct_rule)
        sheet.conditional_formatting.add('K2:K5100', format.pct_rule)
        sheet.conditional_formatting.add('L2:L5100', format.pct_rule)
        sheet.conditional_formatting.add('M2:M2100', format.pct_rule)


        sheet.conditional_formatting.add('N2:N5100', format.pct_rule)
        sheet.conditional_formatting.add('O2:O5100', format.pct_rule)
        sheet.conditional_formatting.add('P2:P5100', format.pct_rule)
        sheet.conditional_formatting.add('Q2:Q5100', format.pct_rule)


    wb.save(lib.data_path + 'drawdown_v.xlsx')

if __name__ == '__main__':
    job()